package cn.may.excel;

import cn.may.exception.BusinessException;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.joda.time.DateTimeUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

/**
 * @author May
 * @date 2020/10/26 15:20
 * @desc excel工具类
 */

public class ExcelUtil {

    public static final String XLS = "xls";
    public static final String XLSX = "xlsx";


    /***
     * 读取excel数据，默认第一行为标题
     * @author mayuanbao
     * @date 2020/10/28 12:34
     * @param file file
     * @return java.util.List<java.util.List < java.lang.String>>
     */
    public static List<List<List<String>>> readExcel(MultipartFile file) throws IOException {
        // 检查文件
        checkFile(file);
        // 创建工作簿
        Workbook wb = getWorkbook(file);
        return getSheets(wb);
    }


    /***
     * 读取excel数据，标题栏所在行rowTitleNum可自定义，行数从0开始
     * @author mayuanbao
     * @date 2020/10/28 12:35
     * @param file file
     * @param rowTitleNum rowTitleNum
     * @return java.util.List<java.util.List < java.lang.String>>
     */

    public static List<List<List<String>>> readExcel(MultipartFile file, int rowTitleNum) throws IOException {
        // 检查文件
        checkFile(file);
        // 创建工作簿
        Workbook wb = getWorkbook(file);
        // 返回读取到的数据
        return getSheets(wb, rowTitleNum);
    }

    /***
     * 获取所有sheet数据，标题栏从0行开始
     * @author mayuanbao
     * @date 2020/10/28 12:39
     * @param wb wb
     * @return java.util.List<java.util.List < java.lang.String>>
     */
    private static List<List<List<String>>> getSheets(Workbook wb) throws IOException {
        List<List<List<String>>> list = new ArrayList<>();
        if (wb != null) {
            for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
                // 获取当前sheet
                Sheet sheet = wb.getSheetAt(sheetNum);
                if (sheet == null) {
                    continue;
                }
                // 根据标题行获取列数
                Row rowTitle = sheet.getRow(0);
                int columnNum = rowTitle.getPhysicalNumberOfCells();
                // 获取当前sheet结束行
                int lastRowNum = sheet.getLastRowNum();
                // 获取标题以下的所有行
                list.add(getRows(sheet, columnNum, lastRowNum, 0));
            }
            wb.close();
        }
        return list;
    }


    /***
     * 获取所有sheet数据，标题栏所在行rowTitle可自定义，行数从0开始
     * @author mayuanbao
     * @date 2020/10/28 12:37
     * @param wb wb
     * @param rowTitleNum rowTitleNum
     * @return java.util.List<java.util.List < java.lang.String>>
     */
    private static List<List<List<String>>> getSheets(Workbook wb, int rowTitleNum) throws IOException {
        List<List<List<String>>> list = new ArrayList<>();
        if (wb != null) {
            for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
                // 获取当前sheet
                Sheet sheet = wb.getSheetAt(sheetNum);
                if (sheet == null) {
                    continue;
                }
                // 根据标题行获取列数
                Row rowTitle = sheet.getRow(rowTitleNum);
                int columnNum = rowTitle.getPhysicalNumberOfCells();
                // 获取当前sheet结束行
                int lastRowNum = sheet.getLastRowNum();
                // 获取标题以下的所有行
                list.add(getRows(sheet, columnNum, lastRowNum, rowTitleNum));
            }
            wb.close();
        }
        return list;
    }

    /***
     * 写入excel，03版
     * @author mayuanbao
     * @date 2020/10/29 11:25
     * @param list list
     * @param sheetName sheetName
     * @return void
     */

    public static void writeExcel03(List<List<List<String>>> list, List<String> sheetName) {
        // 创建工作簿
        Workbook workbook = new HSSFWorkbook();
        // 创建工作表
        for (int sheetNum = 0; sheetNum < list.size(); sheetNum++) {
            Sheet sheet = workbook.createSheet(sheetName.get(sheetNum));
            // 创建行
            for (int rowNum = 0; rowNum < list.get(sheetNum).size(); rowNum++) {
                Row row = sheet.createRow(rowNum);
                // 创建单元格
                for (int cellNum = 0; cellNum < list.get(sheetNum).get(rowNum).size(); cellNum++) {
                    Cell cell = row.createCell(cellNum);
                    cell.setCellValue(list.get(sheetNum).get(rowNum).get(cellNum));
                }
            }
        }
        String currentTime = String.valueOf(DateTimeUtils.currentTimeMillis());
        String fileName = "src/main/resources/" + currentTime + ".xls";
        try (FileOutputStream fileOutputStream = new FileOutputStream(fileName)) {
            workbook.write(fileOutputStream);
        } catch (IOException e) {
            throw new RuntimeException(e.getMessage(), e);
        }
    }

    /***
     * 获取所有行数据
     * @author mayuanbao
     * @date 2020/10/28 12:38
     * @param sheet sheet
     * @param columnNum columnNum
     * @param lastRowNum lastRowNum
     * @param rowTitleNum rowTitleNum
     * @return java.util.List<java.util.List < java.lang.String>>
     */
    private static List<List<String>> getRows(Sheet sheet, int columnNum, int lastRowNum, int rowTitleNum) {
        List<List<String>> list = new ArrayList<>();
        for (int rowNum = rowTitleNum + 1; rowNum <= lastRowNum; rowNum++) {
            // 获取当前行
            Row row = sheet.getRow(rowNum);
            if (row == null) {
                continue;
            }
            List<String> cells = Arrays.asList(new String[columnNum]);
            // 循环当前行
            for (int cellNum = 0; cellNum < columnNum; cellNum++) {
                Cell cell = row.getCell(cellNum);
                cells.set(cellNum, getCellValue(cell));
            }
            list.add(cells);
        }
        return list;
    }


    /***
     * 检查文件
     * @author mayuanbao
     * @date 2020/10/28 11:08
     * @param file file
     * @return void
     */
    public static void checkFile(MultipartFile file) {
        // 判断文件是否存在
        if (file == null) {
            throw new BusinessException("A0700", "用户上传文件异常");
        }
        // 文件名
        String fileName = file.getOriginalFilename();
        // 判断是否是excel文件
        if (fileName != null && !fileName.endsWith(XLS) && !fileName.endsWith(XLSX)) {
            throw new BusinessException("A0701", "用户上传文件类型不匹配");
        }
    }

    /***
     * 判断文件是xls还是xlsx，再返回一个Workbook
     * @author mayuanbao
     * @date 2020-10-27 12:30
     * @param file file
     * @return org.apache.poi.ss.usermodel.Workbook
     */
    public static Workbook getWorkbook(MultipartFile file) {
        // 获取文件名
        String fileName = file.getOriginalFilename();
        // 创建工作簿
        Workbook wb = null;
        // 获取excel文件io流
        try (InputStream is = file.getInputStream()) {
            // 根据文件后缀名不同(xls or xlsx)选择不同的实现类
            if (fileName != null) {
                if (fileName.endsWith(XLS)) {
                    // 2003
                    wb = new HSSFWorkbook(is);
                } else if (fileName.endsWith(XLSX)) {
                    // 2007 及以上
                    wb = new XSSFWorkbook(is);
                }
            }
        } catch (IOException e) {
            throw new RuntimeException(e.getMessage(), e);
        }
        return wb;
    }

    /***
     * 获取单元格数据，并转为String返回
     * @author mayuanbao
     * @date 2020-10-27 15:54
     * @param cell cell
     * @return java.lang.String
     */

    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (null == cell) {
            return cellValue;
        }
        // 判断数据类型
        switch (cell.getCellType()) {
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case NUMERIC:
                // 日期格式
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    cellValue = new DateTime(date).toString("yyyy-MM-dd");
                } else {
                    // 数字
                    cell.setCellType(CellType.STRING);
                    cellValue = cell.getStringCellValue();
                }
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case BLANK:
                cellValue = "";
                break;
            case FORMULA:
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case ERROR:
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;


        }
        return cellValue;
    }


}
